/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package toolutils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;


/**
 *
 * @author Administrator
 */
public class DbManager {
    public static DbManager dbManager = null;
    private Connection conn;
    private Statement stmt;
    DbManager()
    {
        // 加载驱动
        try {
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite://c:/test1.sqlite",null,null);
            // 设置自动提交为false
            //conn.setAutoCommit(false);
            stmt = conn.createStatement();
        } catch (SQLException ex) {
            System.out.println("数据库初始化失败!");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库驱动未找到!");
        }
        if(CreateBookTable())
        {
            //System.out.println("创建总表成功");
        }
    }
    public synchronized static DbManager Intance()
    {
        if(dbManager==null)
        {
            dbManager = new DbManager();
        }
        return dbManager;
    }
    //创建书目表
    public synchronized boolean CreateBookTable()
    {
        String sql = "create table if not exists booktable(id Integer primary key autoincrement,bookname text not null,bookurl text not null,bookauthor text,bookimage text,bookintro text,isupdate Integer default 0)";
        return ExcuteSql(sql);
    }
    //创建单本书
    public synchronized boolean CreateBTable(int index)
    {
        String sql = "create table if not exists book_"+index+"(id Integer primary key autoincrement,chapter text not null,chapterurl text not null,chaptercontent text not null,isread Integer default 0)";
        return ExcuteSql(sql);
    }

    //插入章节
    public synchronized int InsertBTable(int bookid,String chapterTitle,String chapterUrl,String chapterContent)
    {
        int id = -1;
        String insertsql = "insert into book_"+bookid+"('chapter','chapterurl','chaptercontent') values('"+chapterTitle+"','"+chapterUrl+"','"+chapterContent+"')";
        id = ChapterIsExit(bookid,chapterTitle);
        if(id==-1)
        {//不存在，插入
            id = InsertChapter(insertsql,bookid);
        }
        return id;
    }

    public synchronized int getTableCount(int tableid)
    {
        int count = 0;
        if(stmt!=null)
        {
            try {
                String sql = "select max(id) FROM book_" + tableid;
                ResultSet rs = stmt.executeQuery(sql);
                while(rs.next())
                {
                    count = rs.getInt(1);
                }
            } catch (SQLException ex) {
                Logger.getLogger(DbManager.class.getName()).log(Level.SEVERE, null, ex);
            }
        }


        return count;
    }

    //插入书籍信息 （总表）
    public synchronized int InsertBookTable(String bookname,String bookurl,String bookauthor,String bookimg,String bookintro,int update)
    {
        int id = -1;
        String insertsql = "insert into booktable('bookname','bookurl','bookauthor','bookimage','bookintro','isupdate') values('"+bookname+"','"+bookurl+"','"+bookauthor+"','"+bookimg+"','"+bookintro+"',"+update+")";
        id = BookIsExit(bookname);
        if(id!=-1)
        {
            System.out.println("该书已经存在："+bookname);
        }else{
            id = InsertBook(insertsql);
        }
        System.out.println("id="+id);
        return id;
    }

    public synchronized boolean ExcuteSql(String sql)
    {
        if(stmt!=null)
        {
            try {
                stmt.execute(sql);
            } catch (SQLException ex) {
                System.out.println("excute sql failed:"+ex.getMessage()+sql);
            }
        }else{
            System.out.println("stmt is null");
            return false;
        }
        return true;
    }

    public synchronized int InsertBook(String sql)
    {
        int id = -1;
        if(stmt!=null)
        {
            try {
                System.out.println("insertbook...");
                //执行插入
                stmt.execute(sql);
                String sqlsel = "select max(id) FROM booktable";
                ResultSet rs = stmt.executeQuery(sqlsel);
                while(rs.next())
                {
                    id = rs.getInt(1);
                }
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            }
        }
        return id;
    }

    public synchronized ResultSet Excute(String sql)
    {
         if(stmt!=null)
         {
            try {
                ResultSet rs = stmt.executeQuery(sql);
                return rs;
            } catch (SQLException ex) {
                Logger.getLogger(DbManager.class.getName()).log(Level.SEVERE, null, ex);
            }
         }
         return null;
    }

    public synchronized int BookIsExit(String bookname)
    {
        int id = -1;
        try {
            String sql = "select id from booktable where bookname = '" + bookname + "'";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                id = rs.getInt(1);
            }

        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return id;
    }

    public synchronized int ChapterIsExit(int tableid,String title)
    {
        int id = -1;
        try {
            String sql = "select id from book_"+tableid+" where chapter = '" + title + "'";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                id = rs.getInt(1);
            }

        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return id;
    }

    private int InsertChapter(String sql,int tableid) {
        int id = -1;
        if(stmt!=null)
        {
            try {
                System.out.println("insertbook...");
                //执行插入
                stmt.execute(sql);
                String sqlsel = "select max(id) FROM book_"+tableid;
                ResultSet rs = stmt.executeQuery(sqlsel);
                while(rs.next())
                {
                    id = rs.getInt(1);
                }
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            }
        }
        return id;
    }


}
